package com.example.yjhteam.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.example.yjhteam.entity.TeamDataDTO;
import com.example.yjhteam.entity.Teams;
import feign.Param;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

/**
 * <p>
 * 队伍表 Mapper 接口
 * </p>
 *
 * @author ly
 * @since 2023-07-18
 */
public interface TeamsMapper extends BaseMapper<Teams> {

    @Select("select * from teams")
    List<Teams> findAll();

    @Select("select * from teams where team_id=#{team_id}")
    Map<String,Object> findOne(@Param("team_id") int teamId);


    /**
     * 根据战队id查询战队的赛事结果
     * @param id
     * @return
     * @Autor yyh
     */
    @Select("SELECT t1.team_id AS hid, t1.icon AS hicon, t2.team_id AS aid, t2.icon AS aicon, ha.*\n" +
            "            FROM teams t1\n" +
            "            INNER JOIN (\n" +
            "                    SELECT  date,mtc.match_id mid, mtc.home_team_name AS hname, mtc.home_score AS hscore, mtc.away_team_name AS aname, mtc.away_score AS ascore\n" +
            "                    FROM matches mtc\n" +
            "                    WHERE EXISTS (\n" +
            "                            SELECT 1\n" +
            "                            FROM teams t\n" +
            "                            WHERE (mtc.home_team_name = t.`name` OR mtc.away_team_name = t.`name`)\n" +
            "                            AND t.team_id = ${id}\n" +
            "                    )\n" +
            "            ) ha ON t1.`name` = ha.hname\n" +
            "            INNER JOIN teams t2 ON ha.aname = t2.`name` order by date limit ${page},${size} ")
    List<TeamDataDTO> selectMatchResultByTeamId(@org.apache.ibatis.annotations.Param("id") String id,@Param("page") int page, @Param("size") int size);

}
